from sqlalchemy import Column, Integer, String, select, update, delete, insert
import sqlalchemy
from sqlalchemy.ext.asyncio import create_async_engine
import asyncio
from databases import Database
import sqlalchemy.orm
import os
# 全局变量
DATABASE_URL = "sqlite+aiosqlite:///./example.db"
 # 声明模型基类
Base = sqlalchemy.orm.declarative_base()
# 创建数据库引擎
engine = create_async_engine(DATABASE_URL, echo=True)
 # 使用 databases 管理连接
database = Database(DATABASE_URL)

async def create_db():
  """创建数据库表"""
  if os.path.exists("./user.db"):
      print("Database file already exists.")
      return

  async with engine.connect() as conn:
      await conn.run_sync(Base.metadata.create_all)
  print("Database tables created.")

async def init_db():     # 异步连接数据库
    await database.connect() 
    # 检查并创建表
    await create_db()


# 定义模型
class User(Base):
    __tablename__ = "users"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column(String)
    age = sqlalchemy.Column(sqlalchemy.Integer)


# 插入单条数据
async def insert_user(name: str, age: int):
   """
   插入单条用户记录
   """
   async with engine.begin() as conn:
    
    query = insert(User).values(name=name, age=age)
    await conn.execute(query)  # 提交事务

# 查询用户
async def find_by_id(id: int):
   """
   查询用户
   """
   async with engine.connect() as conn:
    query = select(User).filter(User.id == id)
    results = await conn.execute(query)  # 提交事务
    return results.fetchone()

# 更新用户数据
async def update_by_id(id: int, name: str, age: int):
   """
   更新用户数据
   """
   async with engine.begin() as conn:
    query = update(User).where(User.id == id).values(name=name, age=age)
    await conn.execute(query)  # 提交
   
# 更新用户数据
async def delete_by_id(id: int):
   """
   删除用户数据
   """
   async with engine.begin() as conn:
    query = delete(User).where(User.id == id)
    await conn.execute(query)  # 提交
   

async def main():
    await init_db()
    await insert_user('student', 20)

    # user_info = await find_by_id(1)

    # await update_by_id(1, 'student665', 20)

    # await  delete_by_id(1)
    # print(user_info)

    
# 主入口
if __name__ == "__main__":
    asyncio.run(main())